Re: Speeding up LIKE with placeholders?

At 6:33 PM -0400 9/10/04, Tom Lane wrote:
>Dan Sugalski <dan@sidhe.org> writes:
>>  Since the only difference in this case is that the parameters are
>>  pulled out for transport rather than being in band (a
>>  properly-escaped string substitution could turn this case from a
>>  PQexecParams call into a PQexec call) I was thinking the thing to do
>>  would be to either teach the planner to look in the parameter list
>>  when it gets handed $xxx variables, or have the back-end do the
>>  substitution to the SQL before handing the code to the planner.
>
>This has already been considered and rejected.  Oliver Jowett did the
>part that is safe, which is to use the parameter values for estimation
>purposes in other contexts, but pre-substituting a parameter value for
>LIKE calls the mere correctness of the plan into question.

Ouch. Okay, fair 'nuff. (I figured the parameters could be factored
in before the plan was made. Wrongly, I see, now that I poke around
in the code a bit :) Plan B for me it is.

>What it would take to make it workable is a change in the semantics of
>the v3 protocol messages, such that there is no re-use of a plan.  That,
>no one is up for quite yet, when we just hacked the protocol last year ...

It might be possible with a backwards-compatible protocol change, but
that's more work than I'm up for, and this is the wrong list for it
anyway.
--
                Dan

--------------------------------------it's like this-------------------
Dan Sugalski                          even samurai
dan@sidhe.org                         have teddy bears and even
                                       teddy bears get drunk